package com.inventory.repository;

import com.inventory.model.SaleRecord;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;

import java.time.LocalDateTime;
import java.util.List;

@Repository
public interface SaleRecordRepository extends JpaRepository<SaleRecord, Long> {
    
    List<SaleRecord> findByProductId(Long productId);
    
    List<SaleRecord> findBySaleDateBetween(LocalDateTime start, LocalDateTime end);
    
    @Query("SELECT SUM(sr.totalAmount) FROM SaleRecord sr WHERE sr.saleDate BETWEEN ?1 AND ?2")
    Double getTotalSaleAmountBetween(LocalDateTime start, LocalDateTime end);
    
    @Query("SELECT SUM(sr.profit) FROM SaleRecord sr WHERE sr.saleDate BETWEEN ?1 AND ?2")
    Double getTotalProfitBetween(LocalDateTime start, LocalDateTime end);
    
    @Query("SELECT sr FROM SaleRecord sr WHERE sr.product.id = ?1 ORDER BY sr.saleDate DESC")
    List<SaleRecord> findLatestSalesByProductId(Long productId);
    
    @Query("SELECT sr.product.id, SUM(sr.quantity) as totalQuantity FROM SaleRecord sr " +
           "WHERE sr.saleDate BETWEEN ?1 AND ?2 GROUP BY sr.product.id ORDER BY totalQuantity DESC")
    List<Object[]> findTopSellingProducts(LocalDateTime start, LocalDateTime end);
} 